package com.hz.framework.dao;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;

public class DBQueryServ  {

	public DBQuery dbq = DBQuery.getInstance();

	public QueryRunner qr = dbq.getQueryRunner();

	private PreparedStatement ps = null;

	private ResultSet rs = null;

	// ajax从连接池获得结果集
	public ResultSet getResultSet(String sql, String[] values) {
		try {
			ps = dbq.getConnection().prepareStatement(sql);
			for (int i = 0; i < values.length; i++) {
				ps.setString(i + 1, values[i]);
			}
			rs = ps.executeQuery();
		} catch (SQLException e) {
			DBQuery.logger.error(e.getMessage());
		}
		return rs;
	}

	public ResultSet getResultSet(String sql) {
		try {
			Statement st = dbq.getConnection().createStatement();
			rs = st.executeQuery(sql);
		} catch (SQLException e) {
			DBQuery.logger.error(e.getMessage());
		}
		return rs;
	}

	// SQl 插入,更新,删除
	public int updateSql(String sql) {
		// System.out.println(sql);
		int iC = -1;
		try {
			iC = qr.update(sql);
		} catch (SQLException e) {
			DBQuery.logger.error(e.getMessage());
		}
		return iC;
	}

	public boolean updatesqlV(String sql) {
		// System.out.println(sql);
		boolean flag = false;
		ResultSet rs;
		try {
			Statement st = dbq.getConnection().createStatement();
			rs = st.executeQuery(sql);
			if (rs.next()) {
				flag = true;
			}
		} catch (SQLException e) {
			DBQuery.logger.error(e.getMessage());
		}
		return flag;
	}

	public ResultSet executeQuery(String sql) {
		ResultSet rs = null;
		try {
			Statement stmt = dbq.getConnection().createStatement();
			rs = stmt.executeQuery(sql);
		} catch (SQLException e) {
			DBQuery.logger.error(e.getMessage());
		}
		return rs;
	}

	/**
	 * 获取存储过程结果集
	 * 
	 * @param 存储过程名: tabName
	 * @param 存储过程参数值: args
	 * @param 存储过程参数总个数: iC
	 * @return rs;
	 * 
	 */

	public ResultSet rsCall(String tabName, String[] args, int iC) {
		ResultSet rs = null;
		try {

			CallableStatement cs = dbq.getConnection().prepareCall(
					doCallStr(tabName, iC));
			for (int i = 0; i < args.length; i++) {
				cs.setString((i + 1), args[i]);
			}
			cs.execute();
			rs = cs.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}

	/**
	 * 获取存储过程 output 数据
	 * 
	 * @param 存储过程名: tabName
	 * @param 存储过程参数: args
	 * @param 存储过程参数 总个数: iC
	 * @return lstV;
	 * 
	 */

	public List strCall(String tabName, String[] args, int iC) {

		List lstV = new ArrayList();
		try {

			CallableStatement cs = dbq.getConnection().prepareCall(
					doCallStr(tabName, iC));
			for (int i = 0; i < args.length; i++) {
				cs.setString((i + 1), args[i]);
			}

			for (int i = args.length + 1; i <= iC; i++) {

				cs.registerOutParameter(i, Types.OTHER);
			}
			cs.execute();
			for (int i = args.length + 1; i <= iC; i++) {
				lstV.add(cs.getObject(i).toString());
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return lstV;
	}

	/**
	 * 生成 java 存储过程 字符串
	 * 
	 * @param 存储过程名: tabName
	 * @param 存储过程参数总个数: iC
	 * @return sb;
	 * 
	 */
	public String doCallStr(String tabName, int iC) {

		StringBuilder sb = new StringBuilder();

		sb.append("{call ");
		sb.append(tabName);
		sb.append("(");
		for (int i = 1; i <= iC; i++) {
			sb.append("?");
			if (i < iC) {
				sb.append(",");
			}
		}
		sb.append(")");
		sb.append(" }");
		return sb.toString();
	}
}
